const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'suniu'
};

async function columnExists(conn, table, column) {
  const [rows] = await conn.execute(
    `SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?`,
    [dbConfig.database, table, column]
  );
  return rows[0]?.cnt > 0;
}

async function indexExists(conn, table, index) {
  const [rows] = await conn.execute(
    `SELECT COUNT(1) AS cnt FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND INDEX_NAME = ?`,
    [dbConfig.database, table, index]
  );
  return rows[0]?.cnt > 0;
}

async function foreignKeyExists(conn, table, constraintName) {
  const [rows] = await conn.execute(
    `SELECT COUNT(1) AS cnt FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
     WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = ?`,
    [dbConfig.database, table, constraintName]
  );
  return rows[0]?.cnt > 0;
}

async function addColumnIfMissing(conn, table, ddl, logName) {
  const col = ddl.match(/ADD\s+COLUMN\s+`?(\w+)`?/i)?.[1] || ddl.match(/ADD\s+COLUMN\s+(\w+)/i)?.[1];
  if (!col) return;
  if (!(await columnExists(conn, table, col))) {
    await conn.execute(`ALTER TABLE ${table} ${ddl}`);
    console.log(`✅ 已添加列 ${table}.${col}${logName ? ` (${logName})` : ''}`);
  }
}

async function addIndexIfMissing(conn, table, ddl, indexName) {
  if (!(await indexExists(conn, table, indexName))) {
    await conn.execute(`ALTER TABLE ${table} ${ddl}`);
    console.log(`✅ 已添加索引 ${table}.${indexName}`);
  }
}

// 创建/更新数据库表
async function migrate() {
  let connection;
  
  try {
    console.log('🔄 开始数据库迁移...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');

    // 1) 用户表（初次创建）
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        phone VARCHAR(20) UNIQUE NOT NULL COMMENT '手机号',
        token VARCHAR(255) NULL COMMENT '访问令牌',
        refresh_token VARCHAR(255) NULL COMMENT '刷新令牌',
        nickname VARCHAR(100) NULL COMMENT '昵称',
        avatar_url VARCHAR(500) NULL COMMENT '头像URL',
        custom_avatar VARCHAR(500) NULL COMMENT '自定义头像',
        city VARCHAR(100) NULL COMMENT '城市',
        province VARCHAR(100) NULL COMMENT '省份',
        latitude DECIMAL(10,7) NULL COMMENT '纬度',
        longitude DECIMAL(10,7) NULL COMMENT '经度',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_phone (phone),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'
    `);
    console.log('✅ 用户表存在/已创建');

    // 1.1) 用户表增量列补齐
    await addColumnIfMissing(connection, 'users', "ADD COLUMN username VARCHAR(100) NULL COMMENT '用户名'", 'username');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN token VARCHAR(255) NULL COMMENT '访问令牌'", 'token');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN refresh_token VARCHAR(255) NULL COMMENT '刷新令牌'", 'refresh_token');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN nickname VARCHAR(100) NULL COMMENT '昵称'", 'nickname');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN avatar_url VARCHAR(500) NULL COMMENT '头像URL'", 'avatar_url');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN custom_avatar VARCHAR(500) NULL COMMENT '自定义头像'", 'custom_avatar');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN city VARCHAR(100) NULL COMMENT '城市'", 'city');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN province VARCHAR(100) NULL COMMENT '省份'", 'province');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN latitude DECIMAL(10,7) NULL COMMENT '纬度'", 'latitude');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN longitude DECIMAL(10,7) NULL COMMENT '经度'", 'longitude');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP", 'created_at');
    await addColumnIfMissing(connection, 'users', "ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", 'updated_at');

    // 1.2) 用户表索引补齐
    await addIndexIfMissing(connection, 'users', 'ADD UNIQUE KEY uk_users_phone (phone)', 'uk_users_phone');
    await addIndexIfMissing(connection, 'users', 'ADD KEY idx_users_token (token)', 'idx_users_token');

    // 1.3) 兼容历史 users.user_id 列（如存在且为 NOT NULL 无默认值，会导致插入失败）
    if (await columnExists(connection, 'users', 'user_id')) {
      try {
        // 放宽为可空
        await connection.execute(`ALTER TABLE users MODIFY COLUMN user_id INT NULL`);
        console.log('✅ users.user_id 已设置为可空');
      } catch (e) {
        console.warn('ℹ️ users.user_id 可空设置检查:', e?.message || e);
      }

    // 1.4) 兼容历史 users.username 列（如存在且为 NOT NULL 导致插入失败）
    if (await columnExists(connection, 'users', 'username')) {
      try {
        await connection.execute(`ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NULL`);
        console.log('✅ users.username 已设置为可空');
      } catch (e) {
        console.warn('ℹ️ users.username 可空设置检查:', e?.message || e);
      }
      try {
        await connection.execute(`UPDATE users SET username = phone WHERE (username IS NULL OR username = '') AND phone IS NOT NULL`);
        console.log('✅ users.username 空值已用 phone 回填');
      } catch (e) {
        console.warn('ℹ️ users.username 回填检查:', e?.message || e);
      }
    }
      try {
        // 回填：将空值用 id 同步，保持兼容
        await connection.execute(`UPDATE users SET user_id = id WHERE user_id IS NULL`);
        console.log('✅ users.user_id 空值已回填为 id');
      } catch (e) {
        console.warn('ℹ️ users.user_id 回填检查:', e?.message || e);
      }
    }

    // 2) 车辆表（初次创建）
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS vehicles (
        id INT PRIMARY KEY AUTO_INCREMENT,
        vehicle_id VARCHAR(100) UNIQUE NOT NULL COMMENT '车辆ID',
        user_id INT NULL COMMENT '用户ID',
        vin VARCHAR(50) NULL COMMENT 'VIN码',
        model VARCHAR(100) NULL COMMENT '车型',
        brand VARCHAR(50) NULL COMMENT '品牌',
        series VARCHAR(100) NULL COMMENT '系列',
        color VARCHAR(50) NULL COMMENT '颜色',
        year VARCHAR(16) NULL COMMENT '年份',
        mileage BIGINT NULL COMMENT '里程',
        battery_level INT NULL COMMENT '电池电量',
        charging_status VARCHAR(32) NULL COMMENT '充电状态',
        location_lat DECIMAL(10,7) NULL COMMENT '位置纬度',
        location_lng DECIMAL(10,7) NULL COMMENT '位置经度',
        custom_image VARCHAR(500) NULL COMMENT '自定义图片',
        status VARCHAR(20) DEFAULT 'active' COMMENT '状态',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_vehicle_id (vehicle_id),
        INDEX idx_user_id (user_id),
        INDEX idx_status (status),
        INDEX idx_updated_at (updated_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆表'
    `);
    console.log('✅ 车辆表存在/已创建');

    // 2.1) 车辆表增量列补齐
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN user_id INT NULL COMMENT '用户ID'", 'user_id');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN vin VARCHAR(50) NULL COMMENT 'VIN码'", 'vin');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN model VARCHAR(100) NULL COMMENT '车型'", 'model');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN brand VARCHAR(50) NULL COMMENT '品牌'", 'brand');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN series VARCHAR(100) NULL COMMENT '系列'", 'series');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN color VARCHAR(50) NULL COMMENT '颜色'", 'color');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN year VARCHAR(16) NULL COMMENT '年份'", 'year');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN mileage BIGINT NULL COMMENT '里程'", 'mileage');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN battery_level INT NULL COMMENT '电池电量'", 'battery_level');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN charging_status VARCHAR(32) NULL COMMENT '充电状态'", 'charging_status');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN location_lat DECIMAL(10,7) NULL COMMENT '位置纬度'", 'location_lat');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN location_lng DECIMAL(10,7) NULL COMMENT '位置经度'", 'location_lng');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN custom_image VARCHAR(500) NULL COMMENT '自定义图片'", 'custom_image');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN status VARCHAR(20) DEFAULT 'active' COMMENT '状态'", 'status');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP", 'created_at');
    await addColumnIfMissing(connection, 'vehicles', "ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", 'updated_at');

    // 2.2) 车辆表索引补齐
    await addIndexIfMissing(connection, 'vehicles', 'ADD UNIQUE KEY uk_vehicles_vehicle_id (vehicle_id)', 'uk_vehicles_vehicle_id');
    await addIndexIfMissing(connection, 'vehicles', 'ADD KEY idx_vehicles_user_id (user_id)', 'idx_vehicles_user_id');

    // 2.3) 车辆表外键与可空设置
    try {
      // 将 user_id 调整为可空（兼容历史 NOT NULL 的库）
      await connection.execute(`ALTER TABLE vehicles MODIFY COLUMN user_id INT NULL`);
      console.log('✅ vehicles.user_id 已设置为可空');
    } catch (e) {
      // 忽略已为可空等情况
      console.warn('ℹ️ vehicles.user_id 可空设置检查:', e?.message || e);
    }

    // 确保存在 FK：user_id -> users(id) ON DELETE SET NULL ON UPDATE CASCADE
    const fkName = 'fk_vehicles_user_id';
    const hasFk = await foreignKeyExists(connection, 'vehicles', fkName);
    if (!hasFk) {
      // 尝试删除同名或历史自动命名外键（容错）
      try { await connection.execute(`ALTER TABLE vehicles DROP FOREIGN KEY vehicles_ibfk_1`); } catch(_) {}
      try { await connection.execute(`ALTER TABLE vehicles DROP FOREIGN KEY ${fkName}`); } catch(_) {}

      await connection.execute(
        `ALTER TABLE vehicles
         ADD CONSTRAINT ${fkName}
         FOREIGN KEY (user_id) REFERENCES users(id)
         ON DELETE SET NULL ON UPDATE CASCADE`
      );
      console.log('✅ 已建立外键 vehicles.user_id -> users(id) (SET NULL, CASCADE)');
    } else {
      console.log('ℹ️ 外键已存在，跳过添加:', fkName);
    }

    // 3) 代理日志表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS proxy_logs (
        id INT PRIMARY KEY AUTO_INCREMENT,
        request_id VARCHAR(100) COMMENT '请求ID',
        method VARCHAR(10) COMMENT '请求方法',
        path VARCHAR(500) COMMENT '请求路径',
        headers JSON COMMENT '请求头',
        body TEXT COMMENT '请求体',
        response_status INT COMMENT '响应状态码',
        response_body TEXT COMMENT '响应体',
        duration INT COMMENT '耗时(ms)',
        error_message TEXT COMMENT '错误信息',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_request_id (request_id),
        INDEX idx_method (method),
        INDEX idx_response_status (response_status),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代理日志表'
    `);
    console.log('✅ 代理日志表存在/已创建');

    // 4) 访问日志表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS access_logs (
        id INT PRIMARY KEY AUTO_INCREMENT,
        method VARCHAR(10) NOT NULL COMMENT '请求方法',
        path VARCHAR(500) NOT NULL COMMENT '请求路径',
        status INT NOT NULL COMMENT '响应状态码',
        duration_ms INT NULL COMMENT '耗时(ms)',
        ip VARCHAR(64) NULL COMMENT '客户端IP',
        ua VARCHAR(500) NULL COMMENT 'User-Agent',
        user_id INT NULL COMMENT '用户ID',
        phone VARCHAR(32) NULL COMMENT '手机号',
        time DATETIME NULL COMMENT '时间',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_path (path),
        INDEX idx_status (status),
        INDEX idx_user_id (user_id),
        INDEX idx_time (time),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='访问日志表'
    `);
    console.log('✅ 访问日志表存在/已创建');

    // 5) 行为日志表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS action_logs (
        id INT PRIMARY KEY AUTO_INCREMENT,
        level VARCHAR(10) DEFAULT 'INFO' COMMENT '级别',
        type VARCHAR(20) DEFAULT 'action' COMMENT '类型',
        action VARCHAR(100) NOT NULL COMMENT '动作',
        method VARCHAR(10) NULL COMMENT '请求方法',
        path VARCHAR(500) NULL COMMENT '路径',
        ip VARCHAR(64) NULL COMMENT 'IP',
        ua VARCHAR(500) NULL COMMENT 'UA',
        user_id INT NULL COMMENT '用户ID',
        phone VARCHAR(32) NULL COMMENT '手机号',
        meta JSON NULL COMMENT '扩展信息',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_action (action),
        INDEX idx_level (level),
        INDEX idx_user_id (user_id),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行为日志表'
    `);
    console.log('✅ 行为日志表存在/已创建');

    console.log('🎉 数据库迁移完成');
    
  } catch (error) {
    console.error('❌ 数据库迁移失败:', error);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

// 如果直接运行此文件，执行迁移
if (require.main === module) {
  migrate();
}

module.exports = { migrate };
